﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using Director.DomainModels;
using Director.StockSharp.Transaq.Native.Responses;
using TmntsCommon;

namespace Director
{
	internal class PipeStorage : AbstractSqLiteStorage
	{
		readonly Dictionary<KeyValuePair<string, string>, long> _secIds = new Dictionary<KeyValuePair<string, string>, long>();

		public PipeStorage()
			: base(@"D:\\HistoryData\\tmntshistory.sqlite")
		{
			CreateCommand(null, "CREATE TABLE IF NOT EXISTS securities ( id INTEGER PRIMARY KEY AUTOINCREMENT, seccode TEXT, board TEXT)").ExecuteNonQuery();
			CreateCommand(null, "CREATE INDEX IF NOT EXISTS  sec_idx1 ON securities (id)").ExecuteNonQuery();
			CreateCommand(null, "CREATE INDEX IF NOT EXISTS  sec_idx2 ON securities (seccode, board)").ExecuteNonQuery();
		}

		public DateTime? GetLastCandleLastDate(string secCode, string board, int timeFrameLength)
		{
			var id = GetSecurityId(secCode, board);
			return GetLastCandleLastDate(id, timeFrameLength);
		}


		private DateTime? GetLastCandleLastDate(long securityId, int timeFrameLength)
		{
			var candlesTablename = GetCandlesTableName(timeFrameLength);
			
			var cmd = Connection.CreateCommand();
			cmd.CommandText = "SELECT MAX(Date) FROM " + candlesTablename + " WHERE securityId=" + securityId;
			var l = cmd.ExecuteScalar();
			if (l != DBNull.Value && l != null)
			{
				return Convert.ToDateTime(l);
			}
			return null;
		}

		private int GetCandlesCount(long securityId, int timeFrameLength, DateTime from, DateTime to)
		{
			var candlesTablename = GetCandlesTableName(timeFrameLength);

			var cmd = Connection.CreateCommand();
			cmd.CommandText = "SELECT COUNT(*) FROM " + candlesTablename + " WHERE securityId=" + securityId + " AND Date>=:from AND Date<=:to";
			cmd.Parameters.Add(new SQLiteParameter(":from", DbType.DateTime) { Value = from });
			cmd.Parameters.Add(new SQLiteParameter(":to", DbType.DateTime) { Value = to });
			var l = cmd.ExecuteScalar();
			if (l != DBNull.Value && l != null)
			{
				return (int)(long)l;
			}
			return 0;
		}

		private string GetCandlesTableName(int timeFrameLength)
		{
			var candlesTablename = "candles" + timeFrameLength;
			CreateCommand(null, "CREATE TABLE IF NOT EXISTS " + candlesTablename + " ( securityId INTEGER, Date DATETIME, Open SINGLE,High SINGLE,Low SINGLE,Close SINGLE, Volume integer, OpenInterest SINGLE)").ExecuteNonQuery();

			return candlesTablename;
		}


		private int GetSecurityId(string secCode, string board)
		{
			var key = new KeyValuePair<string, string>(secCode, board);
			long value;
			if (!_secIds.TryGetValue(key, out value))
			{
				var cmd = Connection.CreateCommand();
				cmd.CommandText = "SELECT id FROM [securities] WHERE seccode=@seccode AND board=@board";
				cmd.Parameters.Add("@seccode", DbType.String).Value = secCode;
				cmd.Parameters.Add("@board", DbType.String).Value = board;
				var i = cmd.ExecuteScalar();
				if (i != DBNull.Value && i != null)
				{
					value = (long) cmd.ExecuteScalar();
				}
				else
				{
					//using (new Transaction(this))
					{
						value = ExecuteInsertOrUpdate(null, "INSERT INTO [securities] (seccode,board) VALUES (@seccode,@board);",
							new SQLiteParameter("@seccode", DbType.String) {Value = secCode},
							new SQLiteParameter("@board", DbType.String) {Value = board});
					}
				}
				_secIds[key] = value;
			}
			return (int)value;
		}

		public void Write(CandlesResponse candlesResponse, int timeFrameLength)
		{
			var transaqCandles = candlesResponse.Candles as TransaqCandle[] ?? candlesResponse.Candles.ToArray();
			if (transaqCandles.Length == 0) return;
			var min = transaqCandles.Min(e => e.Date);
			var max = transaqCandles.Max(e => e.Date);

			var id = GetSecurityId(candlesResponse.SecCode, candlesResponse.Board);
			var candlesTablename = GetCandlesTableName(timeFrameLength);

			var str = candlesResponse.SecCode + " " + id + "[" + candlesTablename + "] ";
			//if (transaqCandles.Length == MyConnector.FIX_LENGTH)
			{
				var candles = ReadCandles(id, candlesTablename, min, max).OrderBy(c=>c.Date).ToArray();
				var i = 0;
				foreach (var candle in candles)
				{
					if (candle.Date != transaqCandles[i].Date)
					{
						break;
					}
					if (candle.Open != transaqCandles[i].Open)
					{
						break;
					}
					if (candle.Close != transaqCandles[i].Close)
					{
						break;
					}
					if (candle.High != transaqCandles[i].High)
					{
						break;
					}
					if (candle.Low != transaqCandles[i].Low)
					{
						break;
					}
					if (candle.Volume != transaqCandles[i].Volume)
					{
						break;
					}
					i++;
					if (i == transaqCandles.Length)
					{
						break;
					}
				}


				if (i == 0 && candles.Length>0)
				{
					Console.WriteLine("TROUBLES " + str + " i=" + i + " min=" + min + " max=" + max);
					//Проблемы, блять
				}
				if (i < transaqCandles.Length)
				{
					Console.WriteLine("DELETE " + str + " min=" + min + " max=" + max + " delfrom=" + transaqCandles[i].Date);

					min = transaqCandles[i].Date;
					var cmd = Connection.CreateCommand();
					cmd.CommandText = "DELETE FROM " + candlesTablename + " WHERE securityId=" + id + " AND Date>=:from AND Date<=:to";
					cmd.Parameters.Add(new SQLiteParameter(":from", DbType.DateTime) {Value = min});
					cmd.Parameters.Add(new SQLiteParameter(":to", DbType.DateTime) { Value = max });
					cmd.ExecuteNonQuery();

				}
				else
				{
					//видимо ничего делать не надо, все уже есть и оно в порядке
					return;
				}
			}


			{
				var sbi = new SQLiteBulkInsert(Connection, candlesTablename);
				sbi.AddParameter("securityId", DbType.Int16);
				sbi.AddParameter("Date", DbType.DateTime);
				sbi.AddParameter("Open", DbType.Single);
				sbi.AddParameter("High", DbType.Single);
				sbi.AddParameter("Low", DbType.Single);
				sbi.AddParameter("Close", DbType.Single);
				sbi.AddParameter("Volume", DbType.Int32);
				sbi.AddParameter("OpenInterest", DbType.Single);

				var i = 0;
				foreach (var candle in transaqCandles)
				{
					if (candle.Date >= min)
					{
						sbi.Insert(id, candle.Date, candle.Open, candle.High, candle.Low, candle.Close, candle.Volume, candle.Oi);
						i++;
					}
				}
				if (i > 0)
				{
					Console.WriteLine("INSERT " + str + " min=" + min + " count " + i);
					sbi.Flush();
				}
			}
		}

		public IEnumerable<Candle> ReadCandles(Security security, int timeFrameLength, DateTime from)
		{
			string candlesTablename = GetCandlesTableName(timeFrameLength);
			var securityId = GetSecurityId(security.Code, security.Board);
			return ReadCandles(securityId, candlesTablename, from);
		}

		public Candle ReadLastCandle(Security security, int timeFrameLength, DateTime lastOnDate)
		{
			string candlesTablename = GetCandlesTableName(timeFrameLength);
			var securityId = GetSecurityId(security.Code, security.Board);
			
			var cmd = Connection.CreateCommand();
			cmd.CommandText = "SELECT Date, Open, High, Low, Close, Volume, OpenInterest FROM " + candlesTablename + " WHERE securityId=" + securityId + " AND Date<:till ORDER BY Date DESC LIMIT 1";
			cmd.Parameters.Add(new SQLiteParameter(":till", DbType.DateTime) { Value = lastOnDate });

			using (var rdr = cmd.ExecuteReader(CommandBehavior.Default))
			{
				while (rdr.Read())
				{
					return new Candle((DateTime)rdr[0], (decimal)(float)rdr[1], (decimal)(float)rdr[2], (decimal)(float)rdr[3], (decimal)(float)rdr[4], (int)(long)rdr[5], (int)(float)rdr[6]);
				}
			}
			return null;
		}

		public IEnumerable<Candle> ReadCandles(int securityId, string candlesTablename, DateTime from, DateTime? to=null)
		{
			var cmd = Connection.CreateCommand();
			cmd.CommandText = "SELECT Date, Open, High, Low, Close, Volume, OpenInterest FROM " + candlesTablename + " WHERE securityId=" + securityId + " AND Date>=:from";
			cmd.Parameters.Add(new SQLiteParameter(":from", DbType.DateTime) { Value = from });

			if (to.HasValue)
			{
				cmd.CommandText += " AND Date<=:to";
				cmd.Parameters.Add(new SQLiteParameter(":to", DbType.DateTime) { Value = to });
			}

			using (var rdr = cmd.ExecuteReader(CommandBehavior.Default))
			{
				while (rdr.Read())
				{
					yield return new Candle((DateTime)rdr[0], (decimal)(float)rdr[1], (decimal)(float)rdr[2], (decimal)(float)rdr[3], (decimal)(float)rdr[4], (int)(long)rdr[5], (int)(float)rdr[6]);
				}
			}
		}
	}
}

